using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using OgilvyOne.Model;
using OgilvyOne.IDAL;
using System.Collections;
using OgilvyOne.Utility;

namespace OgilvyOne.SqlServerDAL
{
    public class Content
    {
        // Methods
        public void Delete(int con_id)
        {
            string cmdText = "UPDATE con_info SET isdel='1' WHERE Con_ID = " + con_id;
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        public IList getContentList(int Menu_UID)
        {
            string cmdText = "SELECT con_id,tem_id,menu_uid,con_title,source_id,keywords,status,is_hotspot,prior_level,meta,con_desc,con_content,is_first,create_date,con_remark FROM con_info where menu_uid=" + Menu_UID + " and isdel='0' ";
            IList list = new ArrayList();
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                while (reader.Read())
                {
                    ConInfo info = new ConInfo(reader.GetInt32(0), reader.GetInt32(1), reader.GetInt32(2), reader.GetString(3), reader.GetInt32(4), reader.GetString(5), reader.GetString(6), reader.GetString(7), reader.GetInt32(8), reader.GetString(9), reader.GetString(10), reader.GetString(11), reader.GetString(12), reader.GetDateTime(13).ToString());
                    if (!reader.IsDBNull(14))
                    {
                        info.Con_Remark = reader.GetString(14);
                    }
                    list.Add(info);
                }
            }
            return list;
        }

        public int getMaxContentID()
        {
            SqlParameter[] cmdParms = new SqlParameter[0];
            string cmdText = "select max(con_id) from con_info";
            return (int)SQLHelper.ExecuteScalar(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms);
        }

        public void Insert(ConInfo coninfo)
        {
            string cmdText = string.Concat(new object[] { 
            "INSERT INTO con_info(tem_id,menu_uid,con_title,source_id,keywords,status,is_hotspot,prior_level,meta,con_desc,con_content,is_first,create_date,isdel,con_remark) VALUES(", coninfo.Tem_ID, ", ", coninfo.Menu_UID, ",'", StringUtil.ReplaceStr(coninfo.Con_Title), "','", coninfo.Source_ID, "','", StringUtil.ReplaceStr(coninfo.Keywords), "','", coninfo.Status, "','", coninfo.Is_Hotspot, "',", coninfo.Prior_Level, 
            ",'", StringUtil.ReplaceStr(coninfo.Meta), "','", StringUtil.ReplaceStr(coninfo.Con_Desc), "','", StringUtil.ReplaceStr(coninfo.Con_Content), "','", coninfo.Is_First, "',getdate(),'0','", StringUtil.ReplaceStr(coninfo.Con_Remark), "')"
         });
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                    }
                    catch (SqlException exception)
                    {
                        transaction.Rollback();
                        Console.WriteLine(exception.Message);
                    }
                }
            }
        }

        public void Update(ConInfo coninfo)
        {
            string cmdText = string.Concat(new object[] { 
            "UPDATE con_info SET tem_id = ", coninfo.Tem_ID, ",menu_uid = ", coninfo.Menu_UID, ",con_title = '", StringUtil.ReplaceStr(coninfo.Con_Title), "',source_id = '", coninfo.Source_ID, "',keywords ='", StringUtil.ReplaceStr(coninfo.Keywords), "',status='", coninfo.Status, "',is_hotspot='", coninfo.Is_Hotspot, "',prior_level=", coninfo.Prior_Level, 
            ",meta='", coninfo.Meta, "',con_desc='", StringUtil.ReplaceStr(coninfo.Con_Desc), "',con_content='", StringUtil.ReplaceStr(coninfo.Con_Content), "',con_remark='", StringUtil.ReplaceStr(coninfo.Con_Remark), "',is_first='", coninfo.Is_First, "'  WHERE Con_ID =", coninfo.Con_ID
         });
            SqlParameter[] cmdParms = new SqlParameter[0];
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }
    }
}